﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace RollToExcel
{
    public class Exporter
    {
        private readonly FileInfo _databaseQuery;
        private readonly FileInfo _excelTemplate;
        
        public Exporter(FileInfo databaseQuery, FileInfo excelTemplate)
        {
            _databaseQuery = databaseQuery;
            _excelTemplate = excelTemplate;
        }

        public FileInfo Export()
        {
            var proxy = new ExcelExport(_excelTemplate);
            var nameValues = new List<NameValueCollection>();
            
            var data = new DataSet();

            GetRolls(data);

            BuildNameValues(data, nameValues);

            var reportFile = CreateNewReportFile();

            proxy.Export(nameValues);

            return  new FileInfo(reportFile);
        }

        private string CreateNewReportFile()
        {
            var reportFile = _excelTemplate.DirectoryName + Path.GetRandomFileName();
            File.Copy(_excelTemplate.FullName, reportFile);

            return reportFile;
        }

        private static void BuildNameValues(DataSet data, List<NameValueCollection> nameValues)
        {
            for (int i = 0; i < data.Tables[0].Rows.Count; i++)
            {
                var nameValue = new NameValueCollection();
                for (int j = 0; j < data.Tables[0].Columns.Count; j++)
                {
                    nameValue.Add(data.Tables[0].Columns[j].ColumnName, data.Tables[0].Rows[i][j].ToString());
                }

                nameValues.Add(nameValue);
            }
        }

        private void GetRolls(DataSet data)
        {
            using (var sqlConnection =new SqlConnection(Properties.Settings.Default.osdkConnectionString)
                )
            {
                var cmd = new SqlCommand(File.ReadAllText(_databaseQuery.FullName), sqlConnection);

                var adapter = new SqlDataAdapter(cmd);

                adapter.Fill(data);
            }
        }
    }
}
